package cn.melon.mod.dorm.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.melon.Helper.DBHelper;
import cn.melon.base.IBaseExtractor;
import cn.melon.mod.dorm.DormInfo;
import net.ucanaccess.jdbc.UcanaccessSQLException;

public class DormDaolmpl implements IDormDao {
    
    private String sqlCreate = "CREATE TABLE dorm "
            + "(dorm_num TEXT(10),"
            + "student_num TEXT(10),"
            + "new_honor TEXT(10),"
            + "new_score TEXT(10),"
            + "we_charge TEXT(10))";

    
    private DBHelper<DormInfo> dbHelper;
    
    private IBaseExtractor<DormInfo> extractor = new IBaseExtractor<DormInfo>() {

        @Override
        public List<DormInfo> convertToList(ResultSet rs) {
            
            if (rs == null ) return null;
            
            List<DormInfo> infoList = new ArrayList<>();
            try {
                while (rs.next()) {
                    DormInfo info = new DormInfo();
                    
                    info.setDormNum(rs.getString("dorm_num"));
                    info.setStudentNum(rs.getString("student_num"));
                    info.setNewHonor(rs.getString("new_honor"));
                    info.setNewScore(rs.getString("new_score"));
                    info.setWaterAndEletricityCharge(rs.getString("we_charge"));
                    infoList.add(info);
                }
                
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return infoList;
        }
    };

    public void onCreate() {
        try {
            dbHelper.sqlExecute(sqlCreate, new String[]{});
        } catch (UcanaccessSQLException e) {
            e.printStackTrace();
        }
    }
    
    public DormDaolmpl() {
        dbHelper = new DBHelper<>();
    }
    
    public static void main(String[] args) {
        DormDaolmpl dormDao = new DormDaolmpl();
        //DormInfo info = dormDao.getDormInfo("09014311");
        //System.out.println(info.getDormNum());
        
        DormInfo info = new DormInfo();
        info.setDormNum("梅3A-306");
        info.setStudentNum("090143cx");
        info.setNewHonor("校文明宿舍");
        info.setNewScore("98.6");
        info.setWaterAndEletricityCharge("67.3");
        dormDao.updateDormInfo(info);
        
        //dormDao.addDormInfo(info);
        //dormDao.onCreate();
    }

    @Override
    public Boolean updateDormInfo(DormInfo info) {
        
        String sqlUpdate = "UPDATE dorm "
                + "SET dorm_num = ?, new_honor = ?, new_score = ?, we_charge = ?"
                + "where student_num = ?";
        
        String[] paras = new String[5];
        paras[0] = info.getDormNum();
        paras[1] = info.getNewHonor();
        paras[2] = info.getNewScore();
        paras[3] = info.getWaterAndEletricityCharge();
        paras[4] = info.getStudentNum();
        
        return dbHelper.sqlUpdate(sqlUpdate, paras);
    }

    @Override
    public Boolean addDormInfo(DormInfo info) {
        String sqlInsert = "INSERT INTO dorm "
                + "(dorm_num, student_num, new_honor, new_score, we_charge)"
                + "VALUES"
                + "(?, ?, ?, ?, ?)";
        
        String[] paras = new String[5];
        paras[0] = info.getDormNum();
        paras[1] = info.getStudentNum();
        paras[2] = info.getNewHonor();
        paras[3] = info.getNewScore();
        paras[4] = info.getWaterAndEletricityCharge();
        
        return dbHelper.sqlUpdate(sqlInsert, paras);
    }

    @Override
    public DormInfo getDormInfo(String studentNum) {
        
        String sqlQuery = "SELECT * FROM dorm WHERE student_num = ?";
        
        List<DormInfo> infoList = null;
        
        try {
            infoList = dbHelper.sqlQuery(sqlQuery, new String[]{studentNum}, extractor);
        } catch (UcanaccessSQLException e) {
            e.printStackTrace();
        }
        
        if (infoList != null && infoList.size() > 0) {
            return infoList.get(0);
        } else {
            return null;
        }
    }

    @Override
    public List<DormInfo> getAllInfo() {
        String sqlQuery = "SELECT * FROM dorm";
        
        try {
            return dbHelper.sqlQuery(sqlQuery, new String[]{}, extractor);
        } catch (UcanaccessSQLException e) {
            e.printStackTrace();
        }

        return null;
    }

}
